﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.CodeDom.Compiler;

namespace GenericTools.DataExporting
{
    public class ExcelFileExporting : IDisposable
    {
        private DataSet data = null;
        private string documentTitle = string.Empty;
        private TempFileCollection tempFiles;
        private string outputFile = string.Empty;
        private ExcelPackage package = null;

        private ExcelFileExporting()
        {
            tempFiles = new TempFileCollection();
            this.outputFile = tempFiles.AddExtension("xlsx");
            FileInfo newFile = new FileInfo(outputFile);
            package = new ExcelPackage(newFile);
        }

        public ExcelFileExporting(DataTable dataTable) : this()
        {
            this.data = new DataSet();
            this.data.Tables.Add(dataTable.Copy());
        }

        public ExcelFileExporting(DataSet dataset) : this()
        {
            if (dataset == null) this.data = new DataSet();
            else this.data = dataset;
        }

        /// <summary>
        /// Export datatable or dataset object data into the Excel file
        /// </summary>
        /// <returns>Location of Excel file</returns>
        public string Export()
        {
            // Make sure names of the tables are unique.
            MakingSureTableNamesAreUnique();

            ExcelWorkbook workbook = package.Workbook;
            foreach (DataTable dataTable in data.Tables)
            {
                ExcelWorksheet worksheet = workbook.Worksheets.Add(dataTable.TableName);
                // Adding headers to the sheet data.
                int rowIndex = 1;
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    int columnIndex = i + 1;
                    DataColumn column = dataTable.Columns[i];
                    worksheet.Cells[rowIndex, columnIndex].Value = column.ColumnName;
                }

                // Now putting data
                for (int r = 0; r < dataTable.Rows.Count; r++)
                {
                    rowIndex = r + 2;
                    DataRow row = dataTable.Rows[r];
                    for (int c = 0; c < row.ItemArray.Length; c++)
                    {
                        var item = row.ItemArray[c];
                        int columnIndex = c + 1;
                        worksheet.Cells[rowIndex, columnIndex].Value = item;
                    }
                }

                // Do some little formatting on this sheet.
                // Make the top row header and filtering columns
                // Also it should be bold
                int startColumnIndex = 1;
                int endColumnIndex = dataTable.Columns.Count;
                int selectedRow = 1;
                using (var range = worksheet.Cells[selectedRow, startColumnIndex, selectedRow, endColumnIndex])
                {
                    range.Style.Font.Bold = true;
                    range.AutoFilter = true;
                    range.AutoFitColumns();
                }
                worksheet.View.FreezePanes(2, 1);
            }

            // Putting some document properties
            workbook.Properties.Title = documentTitle;
            workbook.Properties.Author = AppConfiguration.ApplicationName;
            workbook.Properties.Company = "Futures Group Global Outreach Tanzania";

            package.Save();
            return outputFile;
        }

        private void MakingSureTableNamesAreUnique()
        {
            HashSet<string> names = new HashSet<string>();
            foreach (DataTable dataTable in data.Tables)
            {
                bool okToContinue = false;
                string tableName = dataTable.TableName;
                if (string.IsNullOrWhiteSpace(tableName))
                {
                    string newName = Guid.NewGuid().ToString();
                    tableName = newName.Replace("-", string.Empty);
                }

                okToContinue = names.Add(tableName);
                if (!okToContinue)
                {
                    // Find any name that will be unique
                    // ahaa, clock ticks!
                    do
                    {
                        long ticks = DateTime.Now.Ticks;
                        tableName = ticks.ToString();
                        okToContinue = names.Add(tableName);
                    } while (!okToContinue);
                }

                dataTable.TableName = tableName;
            }
        }

        #region IDisposable Implementation
        bool disposed = false;
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!disposed)
            {
                package.Dispose();
                tempFiles.Delete();
                disposed = true;
            }
        }
        #endregion
    }
}
